<?php defined('SYSPATH') or die('No direct script access.');

class Top_Orders_Model extends Model {
	
	public function __construct() {		
		$this->db = Database::instance();
	}
	
	public function get_new_albums($top_n=10) {
		try {
			$sql = '
				SELECT * 
				FROM albums
				ORDER BY released_date DESC		
				LIMIT 0, ?			
			';
			$result = $this->db->query($sql, array($top_n))->result(FALSE);
	    	//Kohana::log('debug', Kohana::debug($sql));
	    	
			$albums = array();
			foreach($result as $row) {
				$artist = $this->get_artists_name($row['id']);
				if (mb_strlen($artist, 'UTF-8') > 25) {
					$artist = mb_substr($artist, 0, 25, 'UTF-8') . '...';
				}
				
				$uri = url::base() . $row['cover_picture'];
				$album = array(
					'id' => $row['id'],
					'title' => $row['title'],
					'cover_picture' => $uri,
					'released_date' => $row['released_date'],
					'artist' => $artist,
				);
				array_push($albums, $album);
			}
			
			return $albums;
		}
		catch (Exception $e) {
			Kohana::log('error', $e);
			return null;			
		}
	}
	
	public function get_artists_name($album_id) {
		try {
			$artists = '';
			$sql = '
				SELECT ar.name
				FROM albums ab INNER JOIN artists_albums aa ON ab.id=aa.album_id
				INNER JOIN artists ar ON aa.artist_id=ar.id
				WHERE ab.id=?
				ORDER BY ar.id			
			';
			$result = $this->db->query($sql, array($album_id))->result(FALSE);
			foreach ($result as $row) {
				$artists .= ' ' . $row['name'];
			}
			$artists = trim($artists);
			if (mb_strlen($artists, 'UTF-8')<=0) {
				$artists = 'ไม่ระบุศิลปิน';
			}
			
			return $artists;
		}
		catch (Exception $e) {
			Kohana::log('error', $e);
			return "ไม่ระบุศิลปิน";	
		}
	} 
	
	public function get_new_songs($top_n=10) {
		try {
			$sql = '
				SELECT ab.released_date, a.id as artist_id, a.name as artist_name, a.artists_picture,
				am.album_id, ab.title as album_name, am.track_no, s.title, g.name as gene, s.duration, 
				s.sample_file, s.low_bit_rate_file, s.high_bit_rate_file, s.created, 
				(SELECT Count(*) FROM profiles_download_songs WHERE (album_id=am.album_id) AND (track_no=am.track_no)) as downloaded,
				(SELECT Count(*) FROM profiles_vote_songs WHERE (album_id=am.album_id) AND (track_no=am.track_no)) as voted
				FROM artists a 
				INNER JOIN artists_albums aa ON a.id=aa.artist_id
				INNER JOIN albums ab ON aa.album_id=ab.id
				INNER JOIN artists_songs am ON a.id=am.artist_id 
				INNER JOIN songs s ON (am.album_id=s.album_id) AND (am.track_no=s.track_no)
				INNER JOIN genes g ON s.gene_id=g.id
				ORDER BY ab.released_date DESC, s.album_id, s.track_no
				LIMIT 0, ?			
			';
			
			$result = $this->db->query($sql, array($top_n))->result(FALSE);
			
			$songs = array();
			foreach($result as $row) {
				$artist = $row['artist_name'];
				if (mb_strlen($artist, 'UTF-8') > 15) {
					$artist = mb_substr($artist, 0, 15, 'UTF-8') . '...';
				}
				$track = $row['title'] . ' / ' . $row['album_name'];
				if (mb_strlen($track, 'UTF-8') > 26) {
					$track = mb_substr($track, 0, 26, 'UTF-8') . '...';
				}
				
				$song = array(
					'album_id' => $row['album_id'],
					'track_no' => $row['track_no'],
					'title' => $track,
					'artist' => $artist,
				);
				array_push($songs, $song);
			}
			
	    	//Kohana::log('debug', Kohana::debug($sql));
			return $songs;
		}
		catch (Exception $e) {
			Kohana::log('error', $e);
			return null;			
		}
	}
	
	public function get_song($album_id, $track_no) {
		try {
			$sql = '
				SELECT s.album_id, s.track_no, ab.title as album_name, ab.cover_picture, s.title, s.sample_file, s.low_bit_rate_file, s.high_bit_rate_file
				FROM albums ab INNER JOIN artists_albums aa ON ab.id=aa.album_id
				INNER JOIN artists_songs am ON aa.artist_id=am.artist_id
				INNER JOIN songs s ON am.album_id=s.album_id AND am.track_no=s.track_no
				WHERE s.album_id=? AND s.track_no=?
				AND aa.album_id=s.album_id			
			';	
			$result = $this->db->query($sql, array($album_id, $track_no))->result(FALSE);
			
			$songs = array();

			foreach($result as $row) {
				$uri_album = url::base() . $row['cover_picture'];
				$uri_mp3 = $this->get_file_mp3($album_id, $track_no);
				$song = array(
					'album_id' => $row['album_id'],
					'track_no' => $row['track_no'],
					'title' => $row['title'],
					'album' => $row['album_name'],
					'album_picture' => $uri_album,
					'mp3' => $uri_mp3,
				);
				array_push($songs, $song);
			}
			return $songs;
		}
		catch (Exception $e) {
			Kohana::log('error', $e);
			return null;
		}
	}
	
	public function get_playlist($playlist_id) {
		try {
			$sql = '
				SELECT *
				FROM playlists 
				WHERE id=?			
			';	
			$result = $this->db->query($sql, array($playlist_id))->result(FALSE);
			$playlists = array();

			foreach($result as $row) {
				$uri_logo = url::base() . $row['playlist_logo'];
				$uri_playlist = $this->get_file_playlist($playlist_id);
				$playlist = array(
					'id' => $row['id'],
					'title' => $row['title'],
					'logo' => $uri_logo,
					'playlist' => $uri_playlist,
				);
				array_push($playlists, $playlist);
			}
			return $playlists;
		}
		catch (Exception $e) {
			Kohana::log('error', $e);
			return null;
		}
	}
	
	public function get_file_mp3($album_id, $track_no) {
		try {
			$sql = '
				SELECT *
				FROM songs
				WHERE album_id=? AND track_no=?	
			';	
			$result = $this->db->query($sql, array($album_id, $track_no))->result(FALSE);
			
			$auth = new Auth();
			$uri_mp3 = '';
			foreach($result as $row) {
				if ($auth->logged_in('admin')) {
					$uri_mp3 = url::base() . rawurlencode($row['high_bit_rate_file']);
				}
				else if ($auth->logged_in('vip')) {
					$uri_mp3 = url::base() . rawurlencode($row['high_bit_rate_file']);
				}
				else if ($auth->logged_in('member')) {
					$uri_mp3 = url::base() . rawurlencode($row['low_bit_rate_file']);
				}
				else {
					$uri_mp3 = url::base() . rawurlencode($row['sample_file']);
				}
				$uri_mp3 = str_replace('%2F','/',$uri_mp3);
			}
			return $uri_mp3;
		}
		catch (Exception $e) {
			Kohana::log('error', $e);
			return '';
		}
	} 
	
	public function get_file_playlist($playlist_id) {
		try {
			$sql = '
				SELECT *
				FROM playlists
				WHERE id=?
			';	
			$result = $this->db->query($sql, array($playlist_id))->result(FALSE);
			
			$auth = new Auth();
			$uri_playlist = '';
			foreach($result as $row) {
				if ($auth->logged_in('admin')) {
					$uri_playlist = url::base() . rawurlencode($row['high_bit_rate_file']);
				}
				else if ($auth->logged_in('vip')) {
					$uri_playlist = url::base() . rawurlencode($row['high_bit_rate_file']);
				}
				else if ($auth->logged_in('member')) {
					$uri_playlist = url::base() . rawurlencode($row['low_bit_rate_file']);
				}
				else {
					$uri_playlist = url::base() . rawurlencode($row['sample_file']);
				}
				$uri_playlist = str_replace('%2F','/',$uri_playlist);
			}
			return $uri_playlist;
		}
		catch (Exception $e) {
			Kohana::log('error', $e);
			return '';
		}
	}
	

	public function get_random_channel_playlist_id($owner_id, $channel_id) {
		try {
			$sql = '
				SELECT cp.playlist_id as playlist_id
				FROM channels c INNER JOIN channels_playlists cp ON c.id=cp.channel_id
				WHERE c.owner_id=? AND c.id=?
				ORDER BY RAND()
				LIMIT 1			
			';
			$playlist_id = $this->db->query($sql, array($owner_id, $channel_id))->current()->playlist_id;
	    	
			return $playlist_id;
		}
		catch (Exception $e) {
			Kohana::log('error', $e);
			return '';			
		}
	}
	
	
	public function get_random_channel_playlist($owner_id, $channel_id) {
		try {
			$playlist_id = $this->get_random_channel_playlist_id($owner_id, $channel_id);
	    	
			return $this->get_file_playlist($playlist_id);
		}
		catch (Exception $e) {
			Kohana::log('error', $e);
			return '';			
		}
	}
	
	public function get_random_playlist() {
		try {
			$sql = '
				SELECT id
				FROM playlists
				ORDER BY RAND()
				LIMIT 1			
			';
			$playlist_id = $this->db->query($sql)->current()->id;
	    	//Kohana::log('debug', Kohana::debug($sql));
	    	
			return $this->get_file_playlist($playlist_id);
		}
		catch (Exception $e) {
			Kohana::log('error', $e);
			return '';			
		}
	}
	
	public function get_new_channels($top_n=10) {
		try {
			$sql = '
					SELECT owner_id, id, title, channel_picture, DATE(created) as created
					FROM channels
					ORDER BY created DESC
					LIMIT 0, ?			
			';
			$result = $this->db->query($sql, array($top_n))->result(FALSE);
	    	//Kohana::log('debug', Kohana::debug($sql));
	    	
			$channels = array();
			foreach($result as $row) {				
				$uri = url::base() . $row['channel_picture'];
				$channel = array(
					'owner_id' => $row['owner_id'],
					'id' => $row['id'],
					'title' => $row['title'],
					'channel_picture' => $uri,
					'created' => $row['created'],
				);
				array_push($channels, $channel);
			}
			
			return $channels;
		}
		catch (Exception $e) {
			Kohana::log('error', $e);
			return null;			
		}
		
	}

	public function get_top_new_download($top_n=10) {
		try {
			$sql = '
				SELECT DISTINCT a.id as artist_id, a.name as artist_name, a.artists_picture,
				am.album_id, ab.title as album_name, am.track_no, s.title, g.name as gene, s.duration, 
				s.sample_file, s.low_bit_rate_file, s.high_bit_rate_file, s.created, 
				(SELECT Count(*) FROM profiles_download_songs WHERE (album_id=am.album_id) AND (track_no=am.track_no)) as downloaded,
				(SELECT Count(*) FROM profiles_vote_songs WHERE (album_id=am.album_id) AND (track_no=am.track_no)) as voted
				FROM
				(
				    SELECT album_id, track_no, count(*) as rating
				    FROM profiles_download_songs
				    GROUP BY album_id, track_no
				) r
				INNER JOIN songs s ON (r.album_id=s.album_id) AND (r.track_no=s.track_no)
				INNER JOIN albums ab ON s.album_id=ab.id
				INNER JOIN artists_songs am ON (am.album_id=s.album_id) AND (am.track_no=s.track_no)
				INNER JOIN artists_albums aa ON am.artist_id=aa.artist_id
				INNER JOIN artists a ON aa.artist_id=a.id
				INNER JOIN genes g ON s.gene_id=g.id
				WHERE ab.released_date BETWEEN (DATE_SUB(CURDATE(),INTERVAL 30 DAY)) AND (CURDATE())
				ORDER BY downloaded DESC
				LIMIT 0, ?			
			';
			$result = $this->db->query($sql, array($top_n));
			return $result->result(FALSE);
		}	
		catch (Exception $e) {
			Kohana::log('error', $e);
			return null;
		}	
	}
	
	public function get_top_download($top_n=10) {
		try {
			$sql = '
				SELECT DISTINCT a.id as artist_id, a.name as artist_name, a.artists_picture,
				am.album_id, ab.title as album_name, am.track_no, s.title, g.name as gene, s.duration, 
				s.sample_file, s.low_bit_rate_file, s.high_bit_rate_file, s.created, 
				(SELECT Count(*) FROM profiles_download_songs WHERE (album_id=am.album_id) AND (track_no=am.track_no)) as downloaded,
				(SELECT Count(*) FROM profiles_vote_songs WHERE (album_id=am.album_id) AND (track_no=am.track_no)) as voted
				FROM
				(
				    SELECT album_id, track_no, count(*) as rating
				    FROM profiles_download_songs
				    GROUP BY album_id, track_no
				) r
				INNER JOIN songs s ON (r.album_id=s.album_id) AND (r.track_no=s.track_no)
				INNER JOIN albums ab ON s.album_id=ab.id
				INNER JOIN artists_songs am ON (am.album_id=s.album_id) AND (am.track_no=s.track_no)
				INNER JOIN artists_albums aa ON am.artist_id=aa.artist_id
				INNER JOIN artists a ON aa.artist_id=a.id
				INNER JOIN genes g ON s.gene_id=g.id
				ORDER BY downloaded DESC
				LIMIT 0, ?			
			';
			$result = $this->db->query($sql, array($top_n));
			return $result->result(FALSE);
		}	
		catch (Exception $e) {
			Kohana::log('error', $e);
			return null;
		}	
	}
	
	public function get_top_new_vote($top_n=10) {
		try {
			$sql = '
				SELECT DISTINCT a.id as artist_id, a.name as artist_name, a.artists_picture,
				am.album_id, ab.title as album_name, am.track_no, s.title, g.name as gene, s.duration, 
				s.sample_file, s.low_bit_rate_file, s.high_bit_rate_file, s.created, 
				(SELECT Count(*) FROM profiles_download_songs WHERE (album_id=am.album_id) AND (track_no=am.track_no)) as downloaded,
				(SELECT Count(*) FROM profiles_vote_songs WHERE (album_id=am.album_id) AND (track_no=am.track_no)) as voted
				FROM
				(
				    SELECT album_id, track_no, SUM(rating) as rating
			        FROM profiles_vote_songs
			        GROUP BY album_id, track_no
				) r
				INNER JOIN songs s ON (r.album_id=s.album_id) AND (r.track_no=s.track_no)
				INNER JOIN albums ab ON s.album_id=ab.id
				INNER JOIN artists_songs am ON (am.album_id=s.album_id) AND (am.track_no=s.track_no)
				INNER JOIN artists_albums aa ON am.artist_id=aa.artist_id
				INNER JOIN artists a ON aa.artist_id=a.id
				INNER JOIN genes g ON s.gene_id=g.id
				WHERE ab.released_date BETWEEN (DATE_SUB(CURDATE(),INTERVAL 30 DAY)) AND (CURDATE())
				ORDER BY voted DESC
				LIMIT 0, ?			
			';
			$result = $this->db->query($sql, array($top_n));
			return $result->result(FALSE);
		}	
		catch (Exception $e) {
			Kohana::log('error', $e);
			return null;
		}	
	}
	
	public function get_top_vote($top_n=10) {
		try {
			$sql = '
				SELECT DISTINCT a.id as artist_id, a.name as artist_name, a.artists_picture,
				am.album_id, ab.title as album_name, am.track_no, s.title, g.name as gene, s.duration, 
				s.sample_file, s.low_bit_rate_file, s.high_bit_rate_file, s.created, 
				(SELECT Count(*) FROM profiles_download_songs WHERE (album_id=am.album_id) AND (track_no=am.track_no)) as downloaded,
				(SELECT Count(*) FROM profiles_vote_songs WHERE (album_id=am.album_id) AND (track_no=am.track_no)) as voted
				FROM
				(
				    SELECT album_id, track_no, SUM(rating) as rating
			        FROM profiles_vote_songs
			        GROUP BY album_id, track_no
				) r
				INNER JOIN songs s ON (r.album_id=s.album_id) AND (r.track_no=s.track_no)
				INNER JOIN albums ab ON s.album_id=ab.id
				INNER JOIN artists_songs am ON (am.album_id=s.album_id) AND (am.track_no=s.track_no)
				INNER JOIN artists_albums aa ON am.artist_id=aa.artist_id
				INNER JOIN artists a ON aa.artist_id=a.id
				INNER JOIN genes g ON s.gene_id=g.id
				ORDER BY voted DESC
				LIMIT 0, ?			
			';
			$result = $this->db->query($sql, array($top_n));
			return $result->result(FALSE);
		}	
		catch (Exception $e) {
			Kohana::log('error', $e);
			return null;
		}	
	}
	
	public function get_top_new_channel($top_n=10) {
		try {
			$sql = '
				SELECT c.owner_id, c.id as channel_id, c.title, c.channel_picture, DATE(c.created) as created, SUM(vc.rating) as voted
				FROM profiles_vote_channels vc INNER JOIN channels c ON vc.owner_id=c.owner_id AND vc.channel_id=c.id
				WHERE DATE(c.created) BETWEEN (DATE_SUB(CURDATE(),INTERVAL 30 DAY)) AND (CURDATE())
				GROUP BY c.owner_id, c.id
				ORDER BY voted DESC
				LIMIT 0, ?			
			';
			$result = $this->db->query($sql, array($top_n));
			return $result->result(FALSE);
		}	
		catch (Exception $e) {
			Kohana::log('error', $e);
			return null;
		}	
	}
	
	public function get_top_channel($top_n=10) {
		try {
			$sql = '
				SELECT c.owner_id, c.id as channel_id, c.title, c.channel_picture, DATE(c.created) as created, SUM(vc.rating) as voted
				FROM profiles_vote_channels vc INNER JOIN channels c ON vc.owner_id=c.owner_id AND vc.channel_id=c.id
				GROUP BY c.owner_id, c.id
				ORDER BY voted DESC
				LIMIT 0, ?			
			';
			$result = $this->db->query($sql, array($top_n));
			return $result->result(FALSE);
		}	
		catch (Exception $e) {
			Kohana::log('error', $e);
			return null;
		}	
	}
	
}